package com.leyi.inception.api;

import com.leyi.inception.api.enums.ExecuteModelEnum;
import com.leyi.inception.api.enums.IfBackupEnum;
import com.leyi.inception.api.model.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.text.MessageFormat;
import java.util.*;
import java.util.stream.Collectors;


public class InceptionApi {

    private static final Logger LOGGER = LoggerFactory.getLogger(InceptionApi.class);


    private static final Integer CHECK_MODEL = 0;

    private static final Integer EXECUTE_MODEL = 1;

    //inception连接地址
    private String url = "";
    //inception连接用户名
    private String username = "";
    //inception连接密码
    private String password = "";

    static {
        try {
            Class.forName(InceptionConstant.JDBC_DRIVER);
        } catch (ClassNotFoundException e) {
            LOGGER.error("ClassNotFoundException", e);
        }
    }

    // MySQL 8.0 以上版本 - JDBC 驱动名及数据库 URL
    //static final String jdbcDriver = "com.mysql.cj.jdbc.Driver";
    //static final String url = "jdbc:mysql://localhost:3306/RUNOOB?useSSL=false&serverTimezone=UTC";


    private InceptionApi() {
    }

    public InceptionApi(String host, String port, String username, String password) {
        this.url = InceptionConstant.JDBC_PREV + host + ":" + port;
        this.username = username;
        this.password = password;
    }

    public static InceptionApi init(String host, String port, String user, String password) {
        return new InceptionApi(host, port, user, password);
    }

    public List<InceptionResult> check(String sql, InceptionExecuteDbModel dbModel) {
        return this.execute(sql, ExecuteModelEnum.CHECK, dbModel, IfBackupEnum.NOT_BACKUP.getKey());
    }

    public List<InceptionResult> execute(String sql, InceptionExecuteDbModel dbModel) {
        return this.execute(sql, ExecuteModelEnum.EXECUTE, dbModel, IfBackupEnum.BACKUP.getKey());
    }

    public List<InceptionResult> executeNotBackup(String sql, InceptionExecuteDbModel dbModel) {
        return this.execute(sql, ExecuteModelEnum.EXECUTE, dbModel, IfBackupEnum.NOT_BACKUP.getKey());
    }


    /**
     * 执行inception
     *
     * @param sql          要执行的sql
     * @param executeModel 执行模式，1是
     * @param dbModel      要执行的数据库信息
     */
    public List<InceptionResult> execute(String sql, ExecuteModelEnum executeModel, InceptionExecuteDbModel dbModel, String ifBackup) {
        if (null != sql && !"".equals(sql)) {
            //去除换行，用空格代替
            sql = sql.replaceAll("\r|\n", " ");
        }
        List<InceptionResult> resultModels = new ArrayList<>();
        try (Connection conn = DriverManager.getConnection(url, username, password);
             Statement stmt = conn.createStatement()) {
            List<String> dbParams = new LinkedList<>();
            dbParams.add(dbModel.getUsername());
            dbParams.add(dbModel.getPassword());
            dbParams.add(dbModel.getHost());
            dbParams.add(dbModel.getPort());
            dbParams.add(ifBackup);
            dbParams.add(dbModel.getDbName());
            dbParams.add(sql);
            //转换成inception能够识别的语句
            String sqlContent = MessageFormat.format(executeModel.getValue(), dbParams.toArray(new String[0]));
            LOGGER.info("InceptionExecuteDbModel info:" + dbModel.toString());
            LOGGER.info("sql Content:" + sqlContent);
            ResultSet rs = stmt.executeQuery(sqlContent);
            while (rs.next()) {
                InceptionResult inceptionResult = new InceptionResult();
                inceptionResult.setOrderId(rs.getString("order_id"));
                inceptionResult.setStage(rs.getString("stage"));
                inceptionResult.setErrorLevel(rs.getString("error_level"));
                inceptionResult.setStageStatus(rs.getString("stage_status"));
                inceptionResult.setErrorMessage(rs.getString("error_message"));
                inceptionResult.setSql(rs.getString("sql"));
                inceptionResult.setAffectedRows(rs.getString("affected_rows"));
                inceptionResult.setSequence(rs.getString("sequence"));
                inceptionResult.setBackupDbname(rs.getString("backup_dbname"));
                inceptionResult.setExecuteTime(rs.getString("execute_time"));
                inceptionResult.setSqlsha1(rs.getString("sqlsha1"));
                inceptionResult.setBackupTime(rs.getString("backup_time"));
                resultModels.add(inceptionResult);
                LOGGER.info("inception 执行结果,sql内容：" + inceptionResult.getSql() + "\n执行结果：" + inceptionResult.toString());
            }
        } catch (SQLException e) {
            LOGGER.error("inception连接失败", e);
            throw new RuntimeException(e);
        }
        return resultModels;
    }


    /**
     * 获取备份回滚sql
     *
     * @param dbModel
     * @param sequenceList
     * @return
     */
    public static List<InceptionRollbackSqlModel> getRollbackSql(InceptionBackupDbModel dbModel, List<String> sequenceList) throws SQLException {
        //拼接备份数据名字
        String host = InceptionConstant.JDBC_PREV + dbModel.getHost() + ":" + dbModel.getPort() + "/" + dbModel.getDbName();
        sequenceList = transStrList(sequenceList);
        List<InceptionRollbackSqlModel> rollbackSqlModeList = new ArrayList<>();
        ResultSet rs1 = null;
        ResultSet rs2 = null;
        try (Connection conn = DriverManager.getConnection(host, dbModel.getUsername(), dbModel.getPassword());
             Statement stmt = conn.createStatement()) {
            List<InceptionBackupInfoModel> inceptionBackupInfoModelList = new ArrayList<>();
            String sqlContent = "select * from $_$Inception_backup_information$_$ where opid_time in ({0})";
            sqlContent = MessageFormat.format(sqlContent, listToString(sequenceList, ','));
            //查询sql执行信息
            rs1 = stmt.executeQuery(sqlContent);
            while (rs1.next()) {
                InceptionBackupInfoModel inceptionBackupInfoModel = new InceptionBackupInfoModel();
                inceptionBackupInfoModel.setOpidTime(rs1.getString("opid_time"));
                inceptionBackupInfoModel.setStartBinlogFile(rs1.getString("start_binlog_file"));
                inceptionBackupInfoModel.setStartBinlogPos(rs1.getInt("start_binlog_pos"));
                inceptionBackupInfoModel.setEndBinlogFile(rs1.getString("end_binlog_file"));
                inceptionBackupInfoModel.setEndBinlogPos(rs1.getInt("end_binlog_pos"));
                inceptionBackupInfoModel.setSqlStatement(rs1.getString("sql_statement"));
                inceptionBackupInfoModel.setHost(rs1.getString("host"));
                inceptionBackupInfoModel.setDbname(rs1.getString("dbname"));
                inceptionBackupInfoModel.setTablename(rs1.getString("tablename"));
                inceptionBackupInfoModel.setPort(rs1.getInt("port"));
                inceptionBackupInfoModel.setTime(rs1.getDate("time"));
                inceptionBackupInfoModel.setType(rs1.getString("type"));
                inceptionBackupInfoModelList.add(inceptionBackupInfoModel);
            }
            if (inceptionBackupInfoModelList != null && inceptionBackupInfoModelList.size() > 0) {
                Map<String, List<InceptionBackupInfoModel>> backupInfoMap = inceptionBackupInfoModelList.stream().collect(Collectors.groupingBy(InceptionBackupInfoModel::getTablename));
                //通过执行信息查询回滚sql
                Set<Map.Entry<String, List<InceptionBackupInfoModel>>> entries = backupInfoMap.entrySet();
                for (Map.Entry<String, List<InceptionBackupInfoModel>> entry : entries) {
                    String tableName = entry.getKey();
                    List<InceptionBackupInfoModel> inceptionBackupInfoModels = entry.getValue();
                    sequenceList.clear();
                    for (InceptionBackupInfoModel inceptionBackupInfoModel : inceptionBackupInfoModels) {
                        sequenceList.add("'" + inceptionBackupInfoModel.getOpidTime() + "'");
                    }
                    String sql = "select id,rollback_statement,opid_time from {0} where opid_time in ({1})";
                    sql = MessageFormat.format(sql, tableName, listToString(sequenceList, ','));
                    try (Statement stmt2 = conn.createStatement()) {
                        rs2 = stmt2.executeQuery(sql);
                        while (rs2.next()) {
                            InceptionRollbackSqlModel inceptionRollbackSqlModel = new InceptionRollbackSqlModel();
                            inceptionRollbackSqlModel.setId(rs2.getLong("id"));
                            inceptionRollbackSqlModel.setRollbackStatement(rs2.getString("rollback_statement"));
                            inceptionRollbackSqlModel.setOpidTime(rs2.getString("opid_time"));
                            rollbackSqlModeList.add(inceptionRollbackSqlModel);
                        }
                    }
                }
            }
        } catch (SQLException e) {
            LOGGER.error("inception连接失败", e);
        } finally {
            if (rs2 != null) {
                rs2.close();
            }
            if (rs1 != null) {
                rs1.close();
            }
        }
        return rollbackSqlModeList;
    }


    private static List<String> transStrList(List<String> sequenceList) {
        List<String> list = new ArrayList<>();
        if (sequenceList != null && sequenceList.size() > 0) {
            for (String str : sequenceList) {
                list.add("'" + str + "'");
            }
        }
        return list;
    }

    public static String listToString(List list, char separator) {
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < list.size(); i++) {
            sb.append(list.get(i));
            if (i < list.size() - 1) {
                sb.append(separator);
            }
        }
        return sb.toString();
    }

    public static void main(String[] args) {
        InceptionApi inceptionApi = InceptionApi.init("localhost", "4000", "", "");
        InceptionExecuteDbModel inceptionExecuteDbModel = new InceptionExecuteDbModel();
        inceptionExecuteDbModel.setDbName("t_test");
        inceptionExecuteDbModel.setHost("localhost");
        inceptionExecuteDbModel.setPort("3306");
        inceptionExecuteDbModel.setUsername("root");
        inceptionExecuteDbModel.setPassword("123456");
        List<InceptionResult> checkResultList = inceptionApi.check("update t_table set value=1 where id =1", inceptionExecuteDbModel);
    }

}
